package cn.ucox.web.ms.service.impl;

import cn.ucox.web.ms.Constant;
import cn.ucox.web.ms.config.DataExchangeConfig;
import cn.ucox.web.ms.domain.TableColumn;
import cn.ucox.web.ms.persistence.ITriggerPersistence;
import cn.ucox.web.ms.service.ITriggerService;
import cn.ucox.web.ms.view.ApiResult;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/9/7.
 */
@Service
public class TriggerServiceImpl implements ITriggerService {

    //@Resource
    private ITriggerPersistence triggerPersistence;

    @Resource(name = "pollingJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource
    private DataExchangeConfig.PollingConfig pollingConfig;

    @Override
    public List<String> getTableList(String database, String schemaname) {

        List<String> list = triggerPersistence.getTableList(database,schemaname);
        return list;
    }

    @Override
    public List<TableColumn> getColumnDatas(String tableName,String schemaname) {
        List<TableColumn> list = triggerPersistence.getColumnDatas(tableName,schemaname);
        return list;
    }

    @Override
    public ApiResult<Boolean> createTrigger(String tableName, String att, String[] columnList, String[] triggerTypes,String level) {

        String dbType = pollingConfig.getDbType();

        if(dbType.equalsIgnoreCase("postgresql")){
            for (String triggerType : triggerTypes){
                if(triggerType.equals(Constant.TRIGGER_MODIFY)){
                    triggerPersistence.createPGModTrigger(tableName,att,level);
                }
                if(triggerType.equals(Constant.TRIGGER_DELETE)){
                    triggerPersistence.createPGDeleteTrigger(tableName,att,level);
                }
            }

            return new ApiResult().success("触发器新建成功");
        }

        // TODO 如果需要前台传入字段，则使用此方法
//        List<TableColumn> list = triggerPersistence.getSelectedColumnDatas(tableName,columnList);
        List<TableColumn> list = triggerPersistence.getColumnDatas(tableName,null);
        StringBuffer start = new StringBuffer("'{");
        StringBuffer end = new StringBuffer("}'");
        StringBuffer trigger_mod = new StringBuffer();
        StringBuffer trigger_del = new StringBuffer();


        for (int i = 0; i < list.size(); i++) {
            String newColumnValue = setNewColumnValue(list.get(i));
            String oldColumnValue = setOldColumnValue(list.get(i));

            if(i==0){
                trigger_mod.append("\""+list.get(i).getColumnName()+"\":\"' || "+newColumnValue+" ||'\"");
            }else {
                trigger_mod.append(",\""+list.get(i).getColumnName()+"\":\"' || "+newColumnValue+" ||'\"");
            }

            if(i==0){
                trigger_del.append("\""+list.get(i).getColumnName()+"\":\"' || "+oldColumnValue+" ||'\"");
            }else {
                trigger_del.append(",\""+list.get(i).getColumnName()+"\":\"' || "+oldColumnValue+" ||'\"");
            }
        }

        String trigger_mod_str = String.format("%s%s%s",start.toString(),trigger_mod.toString(),end.toString());
        String trigger_del_str = String.format("%s%s%s",start.toString(),trigger_del.toString(),end.toString());

        int result = 0;
        String syncTableName = "OS_DEX_SYNC";
        if(level.equalsIgnoreCase(Constant.SYNC_LEVEL_HIGH)){
            syncTableName = "OS_DEX_SYNC";
        }else if(level.equalsIgnoreCase(Constant.SYNC_LEVEL_MEDIUM)){
            syncTableName = "OS_DEX_MEDIUM_SYNC";
        }else {
            syncTableName = "OS_DEX_LOW_SYNC";
        }
        for (String triggerType : triggerTypes){
            // TODO 创建触发器名称长度超过30，则会创建失败
            if(triggerType.equals(Constant.TRIGGER_INSERT)){
                try{
                    result = triggerPersistence.createInsertTrigger(tableName,att,trigger_mod_str,syncTableName);
                    result++;
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if(triggerType.equals(Constant.TRIGGER_UPDATE)){
                try{
                    result = triggerPersistence.createUpdateTrigger(tableName,att,trigger_mod_str,syncTableName);
                    result++;
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if(triggerType.equals(Constant.TRIGGER_DELETE)){
                try{
                    result = triggerPersistence.createDeleteTrigger(tableName,att,trigger_del_str,syncTableName);
                    result++;
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        if(result == 0){
            return new ApiResult().failure("触发器新建失败");
        }else {
            return new ApiResult().success("触发器新建成功");
        }

    }

    private String setNewColumnValue(TableColumn column){
        if(column.getDataType().equals("DATE")){
            return "TO_CHAR(:NEW."+column.getColumnName()+",'YYYY-MM-DD HH24:MI:SS')";
        }else if(column.getDataType().contains("TIMESTAMP")){
            return "TO_CHAR(:NEW."+column.getColumnName()+",'YYYY-MM-DD HH24:MI:SS.FF9')";
        }else if(column.getDataType().equals("CHAR")
                || column.getDataType().equals("NCHAR")
                || column.getDataType().equals("VARCHAR2")
                || column.getDataType().equals("VARCHAR")
                || column.getDataType().equals("NVARCHAR2")
                || column.getDataType().equals("CLOB")
                || column.getDataType().equals("NCLOB")){
            return ":NEW."+column.getColumnName()+"";
        }else if(column.getDataType().equals("NUMBER")){
            return ":NEW."+column.getColumnName()+"";
        }

        return ":NEW."+column.getColumnName()+"";
    }

    private String setOldColumnValue(TableColumn column){
        if(column.getDataType().equals("DATE")){
            return "TO_CHAR(:OLD."+column.getColumnName()+",'YYYY-MM-DD HH24:MI:SS')";
        }else if(column.getDataType().contains("TIMESTAMP")){
            return "TO_CHAR(:OLD."+column.getColumnName()+",'YYYY-MM-DD HH24:MI:SS.FF9')";
        }else if(column.getDataType().equals("CHAR")
                || column.getDataType().equals("NCHAR")
                || column.getDataType().equals("VARCHAR2")
                || column.getDataType().equals("VARCHAR")
                || column.getDataType().equals("NVARCHAR2")
                || column.getDataType().equals("CLOB")
                || column.getDataType().equals("NCLOB")){
            return ":OLD."+column.getColumnName()+"";
        }else if(column.getDataType().equals("NUMBER")){
            return ":OLD."+column.getColumnName()+"";
        }

        return ":OLD."+column.getColumnName()+"";
    }
}
